package com.pks.datacenter.utils;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Stream;

import org.junit.Test;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.pks.datacenter.dto.DbTableColumnInfo;
import com.pks.datacenter.entity.basic.DbInfo;
import com.pks.utils.poi.GridDataDto;
import com.pks.utils.poi.GridHeaderDto;
import com.pks.utils.poi.SExcelUtil;


public class Dzzs {
	
	
	@Test
	public void export(){
		String basePath = "G:/dzzs";
		String txtPath = basePath + File.separator + "template" + File.separator + "a.txt";
		List<String> aList = readTextContent(txtPath);
		
		DbInfo target = new DbInfo();
		target.setDb_type(DbInfo.db_type_mysql);
		target.setDb_ip("10.131.2.11");
		target.setDb_port(3306);
		target.setDb_account("root");
		target.setDb_password("T01eQWsUymLx");
		target.setDb_name("certificate");
		int dbtype = target.getDb_type();
		String db_ip = target.getDb_ip();
		int db_port = target.getDb_port();
		String db_name = target.getDb_name();
		String db_account = target.getDb_account();
		String db_password = target.getDb_password();
		
		String templatePath = basePath + File.separator + "template" + File.separator + "blanktemplate.xls";
		
		List<JSONObject> tables = SUtilDb.queryList(dbtype, db_ip, db_port, db_name, db_account, db_password, "show tables");
		for (JSONObject jsonObject : tables) {
			String table_name = jsonObject.getString("TABLE_NAME");
			if (aList.contains(table_name) || table_name.startsWith("t_data")) {
				continue;
			}
//			String table_name = "t_certificate_bigtype";
			List<DbTableColumnInfo> queryTableColumnList = SUtilDb.queryTableColumnList(dbtype, db_ip, db_port, db_name, db_account, db_password, table_name);
			List<GridHeaderDto> headers = new ArrayList<GridHeaderDto>();
			for (DbTableColumnInfo obj : queryTableColumnList) {
				headers.add(new GridHeaderDto(obj.getColumn_name(), obj.getColumn_name()));
			}
			List<List<GridDataDto>> list = new ArrayList<List<GridDataDto>>();
			int start = 0;
			int pagesize = 2000;
			boolean bool = true;
			while (bool) {
				List<JSONObject> dataList = SUtilDb.queryList(dbtype, db_ip, db_port, db_name, db_account, db_password, "select * from " + table_name + " limit " + start + "," + pagesize);
				for (JSONObject obj : dataList) {
					Set<String> keys = obj.keySet();
					List<GridDataDto> rowDatas = new ArrayList<GridDataDto>();
					for (String key : keys) {
						rowDatas.add(new GridDataDto(key, obj.getString(key)));
					}
					list.add(rowDatas);
				}
				if (dataList.size() < pagesize) {
					bool = false;
				} else {
					start = start + pagesize;
				}
			}
			if (list.size() > 0) {
				
				int sub_start = 0;
				int sub_size = 60000;
				int index = 0;
				while (sub_start < list.size()) {
					int toindex = (sub_start+sub_size)<list.size()?(sub_start+sub_size):list.size();
					System.out.println(table_name);
					List<List<GridDataDto>> temp = list.subList(sub_start,toindex);

					String downloadFile = basePath + File.separator + "excel" + File.separator + table_name + (index>0?("_"+index):"") + ".xls";
					File file = new File(downloadFile);
					if (file.exists()) {
						file.delete();
					}
					Map<String,Object> result = new HashMap<String, Object>();
					result.put("headers", headers);
					result.put("grids", temp);
					SExcelUtil.getInstance().exportObj2ExcelByTemplate(null, templatePath, downloadFile, result, false, true);
					
					index++;
					sub_start=toindex;
				}
			}
			writeTextContent(txtPath, table_name);
		}
	}
	
	public static List<String> readTextContent(String filePath){
		List<String> list = new ArrayList<String>();
		try {
			Stream<String> lines = Files.lines(Paths.get(filePath));
			lines.forEach(ele -> {
				list.add(ele);
			});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	} 
	
	public static void writeTextContent(String filePath,String content) {
		FileWriter fw = null;
		try {
			//如果文件存在，则追加内容；如果文件不存在，则创建文件
			File f = new File(filePath);
			fw = new FileWriter(f, true);
		} catch (IOException e) {
			e.printStackTrace();
		}
		PrintWriter pw = new PrintWriter(fw);
		pw.println(content);
		pw.flush();
		try {
			fw.flush();
			pw.close();
			fw.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
